package com.centling.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.centling.business.CustomerBean;
import com.centling.business.OrderInfoImportVO;
import com.centling.core.BaseDAO;
import com.centling.dao.ICustomerDAO;
import com.centling.util.LocalUtils;

@Repository
@Transactional
public class CustomerDAO extends BaseDAO implements ICustomerDAO{
	
	private static Logger log = Logger.getLogger(CustomerDAO.class);
	
	@SuppressWarnings("rawtypes")
	public List findAll(){
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("SELECT *");
		hql.append("FROM t_Organization ");
		Query query = session.createSQLQuery(hql.toString());
		return query.list();
	}

	@Transactional
	public void save(CustomerBean cb, Integer fItemID, Integer fSuperiorID) {
		log.info("save customer invoke...");
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("insert into t_Organization(");
		
		/** 金蝶-公共信息 */
		hql.append("F_system_source,Femployee,Fdepartment,");		
		/** 个人-基本信息 */
		hql.append("FNumber,F_other_customer_code,FName,F_customer_source,F_customer_type,");
		hql.append("FCustType,Fsex,");
		if(LocalUtils.isNotNull(cb.getfBirthday()))					hql.append("FBirthday,FAge,");
		if(LocalUtils.isNotBlank(cb.getFnation()))					hql.append("Fnation,");
		if(LocalUtils.isNotBlank(cb.getF_persional_faith()))		hql.append("F_persional_faith,");
		if(LocalUtils.isNotBlank(cb.getF_personal_taboo()))			hql.append("F_personal_taboo,");
		if(LocalUtils.isNotBlank(cb.getF_education()))				hql.append("F_education,");
		hql.append("F_marital_status,");
		if(LocalUtils.isNotNull(cb.getF_marital_date()))			hql.append("F_marital_date,");
		if(LocalUtils.isNotBlank(cb.getF_memorial_name()))			hql.append("F_memorial_name,");
		if(LocalUtils.isNotNull(cb.getF_memorial_date()))			hql.append("F_memorial_date,");
		if(LocalUtils.isNotBlank(cb.getfComboBox8()))				hql.append("FComboBox8,");
		if(LocalUtils.isNotBlank(cb.getfComboBox9()))				hql.append("FComboBox9,");
		/** 个人-联系信息 */
		if(LocalUtils.isNotBlank(cb.getfCountry()))					hql.append("FCountry,");
		if(LocalUtils.isNotBlank(cb.getF_province()))				hql.append("F_province,");
		if(LocalUtils.isNotBlank(cb.getF_city()))					hql.append("F_city,");
		if(LocalUtils.isNotNull(cb.getfRegionID()))					hql.append("FRegionID,");
		if(LocalUtils.isNotNull(cb.getfProvinceID()))				hql.append("FProvinceID,");
		if(LocalUtils.isNotNull(cb.getfCityID()))					hql.append("FCityID,");
		if(LocalUtils.isNotBlank(cb.getfAddress()))					hql.append("FAddress,");
		if(LocalUtils.isNotBlank(cb.getfPhone()))					hql.append("FPhone,");
		if(LocalUtils.isNotBlank(cb.getfMobile()))					hql.append("FMobile,");
		if(LocalUtils.isNotBlank(cb.getfEmail()))					hql.append("FEmail,");
		if(LocalUtils.isNotBlank(cb.getF_QQ()))						hql.append("F_QQ,");
		if(LocalUtils.isNotBlank(cb.getF_skype()))					hql.append("F_skype,");
		if(LocalUtils.isNotBlank(cb.getF_micro_message()))			hql.append("F_micro_message,");
		if(LocalUtils.isNotBlank(cb.getF_micro_blog()))				hql.append("F_micro_blog,");
		/** 个人-单位信息 */
		if (LocalUtils.isNotBlank(cb.getfWorkUnit()))				hql.append("FWorkUnit,");
		if (LocalUtils.isNotNull(cb.getfTrade()))					hql.append("FTrade,");
		if (LocalUtils.isNotBlank(cb.getF_work()))					hql.append("F_work,");
		if (LocalUtils.isNotBlank(cb.getfDuty()))					hql.append("FDuty,");
		if (LocalUtils.isNotBlank(cb.getfWorkSite()))				hql.append("FWorkSite,");
		if (LocalUtils.isNotBlank(cb.getfBizPhone()))				hql.append("FBizPhone,");
		if (LocalUtils.isNotBlank(cb.getfFax()))					hql.append("FFax,");
		if (LocalUtils.isNotNull(cb.getF_company_size()))			hql.append("F_company_size,");
		if (LocalUtils.isNotBlank(cb.getfHomePage()))				hql.append("FHomePage,");
		/** 个人-业务信息 */
		if (LocalUtils.isNotBlank(cb.getF_dressing_specification()))hql.append("F_dressing_specification,");
		/** 个人-其他信息 */
		if (LocalUtils.isNotBlank(cb.getF_remarks()))				hql.append("F_remarks,");
		/** 个人-喜好页签 */
		// TODO
		/*
		//private String	fPreType;		//喜好类型		下拉菜单	例如：业余爱好、通讯爱好、产品爱好等
		//private String	fPreference;	//喜好内容		直接填写	可以直接对应喜好类型手工填写
		*/		
		/** others */
		if (LocalUtils.isNotNull(fSuperiorID))						hql.append("FSuperiorID,");
		hql.append("FItemID,Fstatus,FRegUserID,FRegDate) values(");

		
		
		/** 金蝶-公共信息 */
		hql.append(":F_system_source,:Femployee,:Fdepartment,");
		/** 个人-基本信息 */
		hql.append(":FNumber,:F_other_customer_code,:FName,:F_customer_source,:F_customer_type,");
		hql.append(":FCustType,:Fsex,");
		if(LocalUtils.isNotNull(cb.getfBirthday()))					hql.append(":FBirthday,:FAge,");
		if(LocalUtils.isNotBlank(cb.getFnation()))					hql.append(":Fnation,");
		if(LocalUtils.isNotBlank(cb.getF_persional_faith()))		hql.append(":F_persional_faith,");
		if(LocalUtils.isNotBlank(cb.getF_personal_taboo()))			hql.append(":F_personal_taboo,");
		if(LocalUtils.isNotBlank(cb.getF_education()))				hql.append(":F_education,");
		hql.append(":F_marital_status,");
		if(LocalUtils.isNotNull(cb.getF_marital_date()))			hql.append(":F_marital_date,");
		if(LocalUtils.isNotBlank(cb.getF_memorial_name()))			hql.append(":F_memorial_name,");
		if(LocalUtils.isNotNull(cb.getF_memorial_date()))			hql.append(":F_memorial_date,");
		if(LocalUtils.isNotBlank(cb.getfComboBox8()))				hql.append(":FComboBox8,");
		if(LocalUtils.isNotBlank(cb.getfComboBox9()))				hql.append(":FComboBox9,");
		/** 个人-联系信息 */
		if(LocalUtils.isNotBlank(cb.getfCountry()))					hql.append(":FCountry,");
		if(LocalUtils.isNotBlank(cb.getF_province()))				hql.append(":F_province,");
		if(LocalUtils.isNotBlank(cb.getF_city()))					hql.append(":F_city,");
		if(LocalUtils.isNotNull(cb.getfRegionID()))					hql.append(":FRegionID,");
		if(LocalUtils.isNotNull(cb.getfProvinceID()))				hql.append(":FProvinceID,");
		if(LocalUtils.isNotNull(cb.getfCityID()))					hql.append(":FCityID,");
		if(LocalUtils.isNotBlank(cb.getfAddress()))					hql.append(":FAddress,");
		if(LocalUtils.isNotBlank(cb.getfPhone()))					hql.append(":FPhone,");
		if(LocalUtils.isNotBlank(cb.getfMobile()))					hql.append(":FMobile,");
		if(LocalUtils.isNotBlank(cb.getfEmail()))					hql.append(":FEmail,");
		if(LocalUtils.isNotBlank(cb.getF_QQ()))						hql.append(":F_QQ,");
		if(LocalUtils.isNotBlank(cb.getF_skype()))					hql.append(":F_skype,");
		if(LocalUtils.isNotBlank(cb.getF_micro_message()))			hql.append(":F_micro_message,");
		if(LocalUtils.isNotBlank(cb.getF_micro_blog()))				hql.append(":F_micro_blog,");
		/** 个人-单位信息 */
		if (LocalUtils.isNotBlank(cb.getfWorkUnit()))				hql.append(":FWorkUnit,");
		if (LocalUtils.isNotNull(cb.getfTrade()))					hql.append(":FTrade,");
		if (LocalUtils.isNotBlank(cb.getF_work()))					hql.append(":F_work,");
		if (LocalUtils.isNotBlank(cb.getfDuty()))					hql.append(":FDuty,");
		if (LocalUtils.isNotBlank(cb.getfWorkSite()))				hql.append(":FWorkSite,");
		if (LocalUtils.isNotBlank(cb.getfBizPhone()))				hql.append(":FBizPhone,");
		if (LocalUtils.isNotBlank(cb.getfFax()))					hql.append(":FFax,");
		if (LocalUtils.isNotNull(cb.getF_company_size()))			hql.append(":F_company_size,");
		if (LocalUtils.isNotBlank(cb.getfHomePage()))				hql.append(":FHomePage,");
		/** 个人-业务信息 */
		if (LocalUtils.isNotBlank(cb.getF_dressing_specification()))hql.append(":F_dressing_specification,");
		/** 个人-其他信息 */
		if (LocalUtils.isNotBlank(cb.getF_remarks()))				hql.append(":F_remarks,");
		/** 个人-喜好页签 */
		// TODO
		/*
		//private String	fPreType;		//喜好类型		下拉菜单	例如：业余爱好、通讯爱好、产品爱好等
		//private String	fPreference;	//喜好内容		直接填写	可以直接对应喜好类型手工填写
		*/		
		/** others */
		if (LocalUtils.isNotNull(fSuperiorID))						hql.append(":FSuperiorID,");
		hql.append(":FItemID,:Fstatus,:FRegUserID,GETDATE())");
		

		log.info("hql: "+hql.toString());
		Query query = session.createSQLQuery(hql.toString());
		
		/** 金蝶-公共信息 */
		query.setParameter("F_system_source", cb.getF_system_source());
		query.setParameter("Femployee", 255);
		query.setParameter("Fdepartment", 254);
		/** 个人-基本信息 */
		query.setParameter("FNumber", cb.getF_other_customer_code());
		query.setParameter("F_other_customer_code", cb.getF_other_customer_code());
		query.setParameter("FName", cb.getfName());
		query.setParameter("F_customer_source", cb.getF_customer_source());
		query.setParameter("F_customer_type", cb.getF_customer_type());
		query.setParameter("FCustType", 36775);
		query.setParameter("Fsex", (cb.getFsex()==null||(!cb.getFsex().equals("0")&&!cb.getFsex().equals("1")))?"1":cb.getFsex());
		if(LocalUtils.isNotNull(cb.getfBirthday())){				
			query.setParameter("FBirthday", cb.getfBirthday());	
			query.setParameter("FAge", cb.getfAge());
		}
		if(LocalUtils.isNotBlank(cb.getFnation()))					query.setParameter("Fnation", cb.getFnation());
		if(LocalUtils.isNotBlank(cb.getF_persional_faith()))		query.setParameter("F_persional_faith", cb.getF_persional_faith());
		if(LocalUtils.isNotBlank(cb.getF_personal_taboo()))			query.setParameter("F_personal_taboo", cb.getF_personal_taboo());
		if(LocalUtils.isNotBlank(cb.getF_education()))				query.setParameter("F_education", cb.getF_education());
		query.setParameter("F_marital_status", (cb.getF_marital_status()==null||(!cb.getF_marital_status().equals("未婚")&&!cb.getF_marital_status().equals("已婚")))?"保密":cb.getF_marital_status());
		if(LocalUtils.isNotNull(cb.getF_marital_date()))			query.setParameter("F_marital_date", cb.getF_marital_date());
		if(LocalUtils.isNotBlank(cb.getF_memorial_name()))			query.setParameter("F_memorial_name", cb.getF_memorial_name());
		if(LocalUtils.isNotNull(cb.getF_memorial_date()))			query.setParameter("F_memorial_date", cb.getF_memorial_date());
		if(LocalUtils.isNotBlank(cb.getfComboBox8()))				query.setParameter("FComboBox8", cb.getfComboBox8());
		if(LocalUtils.isNotBlank(cb.getfComboBox9()))				query.setParameter("FComboBox9", cb.getfComboBox9());
		/** 个人-联系信息 */
		if(LocalUtils.isNotBlank(cb.getfCountry()))					query.setParameter("FCountry", cb.getfCountry());
		if(LocalUtils.isNotBlank(cb.getF_province()))				query.setParameter("F_province", cb.getF_province());
		if(LocalUtils.isNotBlank(cb.getF_city()))					query.setParameter("F_city", cb.getF_city());
		if(LocalUtils.isNotNull(cb.getfRegionID())&&cb.getfRegionID()!=-1)		query.setParameter("FRegionID", cb.getfRegionID());
		if(LocalUtils.isNotNull(cb.getfProvinceID())&&cb.getfProvinceID()!=-1)	query.setParameter("FProvinceID", cb.getfProvinceID());
		if(LocalUtils.isNotNull(cb.getfCityID())&&cb.getfCityID()!=-1)			query.setParameter("FCityID", cb.getfCityID());
		if(LocalUtils.isNotBlank(cb.getfAddress())){
			query.setParameter("FAddress", cb.getfAddress());
		}
		if(LocalUtils.isNotBlank(cb.getfPhone()))					query.setParameter("FPhone", cb.getfPhone());
		if(LocalUtils.isNotBlank(cb.getfMobile()))					query.setParameter("FMobile", cb.getfMobile());
		if(LocalUtils.isNotBlank(cb.getfEmail()))					query.setParameter("FEmail", cb.getfEmail());
		if(LocalUtils.isNotBlank(cb.getF_QQ()))						query.setParameter("F_QQ", cb.getF_QQ());
		if(LocalUtils.isNotBlank(cb.getF_skype()))					query.setParameter("F_skype", cb.getF_skype());
		if(LocalUtils.isNotBlank(cb.getF_micro_message()))			query.setParameter("F_micro_message", cb.getF_micro_message());
		if(LocalUtils.isNotBlank(cb.getF_micro_blog()))				query.setParameter("F_micro_blog", cb.getF_micro_blog());	
		/** 个人-单位信息 */
		if (LocalUtils.isNotBlank(cb.getfWorkUnit()))				query.setParameter("FWorkUnit", cb.getfWorkUnit());
		if (LocalUtils.isNotNull(cb.getfTrade()))					query.setParameter("FTrade", cb.getfTrade());
		if (LocalUtils.isNotBlank(cb.getF_work()))					query.setParameter("F_work", cb.getF_work());
		if (LocalUtils.isNotBlank(cb.getfDuty()))					query.setParameter("FDuty", cb.getfDuty());
		if (LocalUtils.isNotBlank(cb.getfWorkSite()))				query.setParameter("FWorkSite", cb.getfWorkSite());
		if (LocalUtils.isNotBlank(cb.getfBizPhone()))				query.setParameter("FBizPhone", cb.getfBizPhone());
		if (LocalUtils.isNotBlank(cb.getfFax()))					query.setParameter("FFax", cb.getfFax());
		if (LocalUtils.isNotNull(cb.getF_company_size()))			query.setParameter("F_company_size", cb.getF_company_size());
		if (LocalUtils.isNotBlank(cb.getfHomePage()))				query.setParameter("FHomePage", cb.getfHomePage());	
		/** 个人-业务信息 */
		if (LocalUtils.isNotBlank(cb.getF_dressing_specification()))query.setParameter("F_dressing_specification", cb.getF_dressing_specification());
		/** 个人-其他信息 */
		if (LocalUtils.isNotBlank(cb.getF_remarks()))				query.setParameter("F_remarks", cb.getF_remarks());
		/** 个人-喜好页签 */
		// TODO
		/*
		//private String	fPreType;		//喜好类型		下拉菜单	例如：业余爱好、通讯爱好、产品爱好等
		//private String	fPreference;	//喜好内容		直接填写	可以直接对应喜好类型手工填写
		*/		
		/** others */
		if (LocalUtils.isNotNull(fSuperiorID))						query.setParameter("FSuperiorID", fSuperiorID);
		query.setParameter("FItemID", fItemID);
		query.setParameter("Fstatus", 1072);
		query.setParameter("FRegUserID", 16402);	
		
		query.executeUpdate();
		
		log.info("save customer end.");
	}

	@SuppressWarnings("rawtypes")
	public Integer getFItemID(Integer dsuserid) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select FItemID from t_Organization where FNumber =:FNumber ");
		Query query = session.createSQLQuery(hql.toString())
				.setParameter("FNumber", "DS"+LocalUtils.getSetLengthStr(String.valueOf(dsuserid), 8));
		List list = query.list();
		if (list!=null && !list.isEmpty()){
			return Integer.valueOf(list.get(0).toString());
		} 
		return -1;
	}

	@SuppressWarnings("rawtypes")
	public Integer findFItemIDByPhone(String phone) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select FItemID from t_Organization where FPhone =:FPhone ");
		Query query = session.createSQLQuery(hql.toString())
				.setParameter("FPhone", phone);
		List list = query.list();
		if (list!=null && !list.isEmpty()){
			return Integer.valueOf(list.get(0).toString());
		} 
		return -1;
	}

	@SuppressWarnings("rawtypes")
	public OrderInfoImportVO findPartCustomerInfoByF_other_customer_code(String f_other_customer_code) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select FItemID,Femployee,Fdepartment from t_Organization where F_other_customer_code =:F_other_customer_code ");
		Query query = session.createSQLQuery(hql.toString())
				.setParameter("F_other_customer_code", f_other_customer_code);
		List list = query.list();
		OrderInfoImportVO vo = null;
		if (list!=null && !list.isEmpty()){
			vo = new OrderInfoImportVO();
			Object[] arr = (Object[]) list.get(0);
			vo.setfItemID(arr[0]==null?null:Integer.valueOf(arr[0].toString()));
			vo.setFemployee(arr[1]==null?null:Integer.valueOf(arr[1].toString()));
			vo.setFdepartment(arr[2]==null?null:Integer.valueOf(arr[2].toString()));
			return vo;
		} 
		return vo;
	}

	@SuppressWarnings("rawtypes")
	public Integer findFItemIDByF_other_customer_code(String f_other_customer_code) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select FItemID from t_Organization where F_other_customer_code =:F_other_customer_code ");
		Query query = session.createSQLQuery(hql.toString())
				.setParameter("F_other_customer_code", f_other_customer_code);
		List list = query.list();
		if (list!=null && !list.isEmpty()){
			return Integer.valueOf(list.get(0).toString());
		} 
		return -1;
	}

	@SuppressWarnings("rawtypes")
	public Integer getFCurrencyIDByCustomreID(Integer customerID) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select F_settlement_1 from t_Organization where FItemID =:FItemID ");
		Query query = session.createSQLQuery(hql.toString())
				.setParameter("FItemID", customerID);
		List list = query.list();
		if (list!=null && !list.isEmpty()){
			return Integer.valueOf(list.get(0).toString());
		} 
		return 1;
	}

	public void updateFlastTradeDate(Integer customerID) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("update t_Organization ");
		hql.append("set FlastTradeDate=:FlastTradeDate ");
		hql.append("where FItemID=:FItemID");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		session.createSQLQuery(hql.toString())
			.setParameter("FlastTradeDate", sdf.format(new Date()))
			.setParameter("FItemID", String.valueOf(customerID))
			.executeUpdate();
	}
}
